﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Data.SqlClient;
using System.Diagnostics;
using System.ComponentModel;

namespace SqlFileLayoutViewer {

    class PageAnalyzer {

        // Class to scan and summarize a portion of the data pages in the database
        // Works a little like the "SqlDataReader" model - in order to be called easily 
        // from a BackgroundWorker.DoWork method, separate methods are provided
        // to initialize, read a page incrementally (called in a loop), then close

        public PageAnalyzer(SqlConnectionStringBuilder conString, DBPageRange range) {

            this.conString = conString;
            this._range = range;
            this.CurrentPage = _range.StartPage;
            this.scon = new SqlConnection(conString.ToString());

            // Configure parameterized SQL commands for DBCC Page

            TraceOnCmd = new SqlCommand("DBCC TRACEON (3604);", scon);

            DBCCPageCmd = new SqlCommand("DBCC PAGE (@DBName, @fileid, @pageid, @style);", scon);
            DBCCPageCmd.Parameters.Add(new SqlParameter("DBName", conString.InitialCatalog));
            DBCCPageCmd.Parameters.Add(new SqlParameter("fileid", 0));
            DBCCPageCmd.Parameters.Add(new SqlParameter("pageid", 0));
            DBCCPageCmd.Parameters.Add(new SqlParameter("style", 0));

            DBCCPageCmd.Parameters["fileid"].Value = range.FileID;
            DBCCPageCmd.Parameters["pageid"].Value = 0;
            DBCCPageCmd.Parameters["style"].Value = 0;

        }

        public void open() {

            // Open a connection to the DB and prep for scanning pages


            scon.Open();

            // Prep to run DBCC PAGE and direct its output to messages

            TraceOnCmd.ExecuteNonQuery();

            // Register event handler to receive/process sql info messages (the "message"
            // window content from SSMS, print statement output and errors)

            scon.InfoMessage += new SqlInfoMessageEventHandler(scon_InfoMessage);


        }

        public PageInfo read() {

            DBCCPageCmd.Parameters["pageid"].Value = CurrentPage;

            // Run DBCC PAGE to populate PageInfoBuffer:
            DBCCPageCmd.ExecuteNonQuery();

            // Summarize the content of PageInfoBuffer
            PageInfo pi = AnalyzePage(PageInfoBuffer);

            // Advant to the next page
            CurrentPage++;
            if (CurrentPage > _range.EndPage) _HasMore = false;
 
            return( pi );

        }

        public void close() {
            scon.Close();
        }


        Int64 CurrentPage;

        SqlConnection scon;
        SqlCommand TraceOnCmd;
        SqlCommand DBCCPageCmd;

        private DBPageRange _range;

        public DBPageRange Range {
            get {
                return _range;
            }
        }

        private PageInfo AnalyzePage(String PageInfoBuffer) {

            PageInfo pi = new PageInfo();

            // See if this page is allocated (stale unallocated pages or pages "captured"
            // from other data through instant file initialization will return misleading 
            // results even for fileid and pageid)

            Match m = FindGAMAlloc.Match(PageInfoBuffer);
            pi.GAMAllocated = m.Value.Contains("NOT ALLOCATED") ? false : true;

            m = FindSGAMAlloc.Match(PageInfoBuffer);
            pi.SGAMAllocated = m.Value.Contains("NOT ALLOCATED") ? false : true;

            m = FindPageType.Match(PageInfoBuffer);

            if (!Int32.TryParse(m.Value, out pi.PageType)) {
                pi.PageType = -1;
                Debug.WriteLine("Failed to parse page type");
            }

            if (pi.GAMAllocated || pi.SGAMAllocated) {

                m = FindPageNo.Match(PageInfoBuffer);
                String[] temp = m.Value.ToString().Split(':');

                if (!Int32.TryParse(temp[0], out pi.FileID)) {
                    pi.FileID = -1;
                    Debug.WriteLine("Failed to parse file id");
                }

                if (!Int64.TryParse(temp[1], out pi.PageID)) {
                    pi.PageID = -1;
                    Debug.WriteLine("Failed to parse page id");
                }

                m = FindObjectID.Match(PageInfoBuffer);

                if (!Int32.TryParse(m.Value, out pi.ObjectID)) {
                    pi.ObjectID = -1;
                    Debug.WriteLine("Failed to parse object id");
                }

                m = FindIndexID.Match(PageInfoBuffer);

                if (!Int32.TryParse(m.Value, out pi.IndexID)) {
                    pi.IndexID = -1;
                    Debug.WriteLine("Failed to parse index id");
                }

                m = FindPagePrev.Match(PageInfoBuffer);
                temp = m.Value.ToString().Split(':');

                if (!Int32.TryParse(temp[0], out pi.PrevFile)) {
                    pi.PrevFile = -1;
                    Debug.WriteLine("Failed to parse previous file id");
                }

                if (!Int64.TryParse(temp[1], out pi.PrevPage)) {
                    pi.PrevPage = -1;
                    Debug.WriteLine("Failed to parse previous page id");
                }

                m = FindPageNext.Match(PageInfoBuffer);
                temp = m.Value.ToString().Split(':');

                if (!Int32.TryParse(temp[0], out pi.NextFile)) {
                    pi.NextFile = -1;
                    Debug.WriteLine("Failed to parse next file id");
                }

                if (!Int64.TryParse(temp[1], out pi.NextPage)) {
                    pi.NextPage = -1;
                    Debug.WriteLine("Failed to parse next page id");
                }

                m = FindPartitionID.Match(PageInfoBuffer);

                if (!Int64.TryParse(m.Value, out pi.PartitionID)) {
                    pi.PartitionID = -1;
                    Debug.WriteLine("Failed to parse partition id");
                }

                m = FindSlotCnt.Match(PageInfoBuffer);

                if (!Int32.TryParse(m.Value, out pi.SlotCnt)) {
                    pi.SlotCnt = -1;
                    Debug.WriteLine("Failed to parse slot count");
                }

                m = FindFreeSpace.Match(PageInfoBuffer);
                int tempFreeSpaceBytes = 0;
                if (Int32.TryParse(m.Value, out tempFreeSpaceBytes)) {
                    // Possible data bytes on a page = 8k page = 96 byte header - slot array size
                    // where slot array size = 2 bytes per slot * slot count
                    // Percent free space = ( free space bytes / possible data bytes ) * 100
                    if ( pi.SlotCnt >= 0 ) {
                        pi.FreeSpace = (Int32)( tempFreeSpaceBytes / ( 8192m - 96 - (2 * pi.SlotCnt) ) * 100 );
                    };
                }
                else {
                    pi.FreeSpace = -1;
                    Debug.WriteLine("Failed to parse free space");
                }

            }
            else {
                //pi.PageType = 0;
                pi.FileID = _range.FileID;
                pi.PageID = CurrentPage;
            }
            return pi;
        }
        private SqlConnectionStringBuilder conString;

        private string PageInfoBuffer;

        // Expressions to parse values out of the output text from DBCC commands

        private Regex FindPageType = new Regex(@"(?<=\bm_type = )[0-9]+");

        private Regex FindGAMAlloc = new Regex(@"GAM \([0-9:]+\) = (ALLOCATED|NOT ALLOCATED)");
        private Regex FindSGAMAlloc = new Regex(@"SGAM \([0-9:]+\) = (ALLOCATED|NOT ALLOCATED)");

        private Regex FindPageNo = new Regex(@"(?<=\bm_pageId = \()[0-9:]+");
        private Regex FindPagePrev = new Regex(@"(?<=\bm_prevPage = \()[0-9:]+");
        private Regex FindPageNext = new Regex(@"(?<=\bm_nextPage = \()[0-9:]+");

        private Regex FindFreeSpace = new Regex(@"(?<=\bm_freeCnt = )[0-9]+");
        private Regex FindSlotCnt = new Regex(@"(?<=\bm_slotCnt = )[0-9]+");

        private Regex FindPartitionID = new Regex(@"(?<=\bMetadata: PartitionId = )[0-9:]+");
        private Regex FindObjectID = new Regex(@"(?<=\bMetadata: ObjectId = )[0-9:]+");
        private Regex FindIndexID = new Regex(@"(?<=\bMetadata: IndexId = )[0-9\-:]+");

        private bool _HasMore = true;

        public bool HasMore {
            get {
                return _HasMore;
            }
        }

        // Events and handlers

        private void scon_InfoMessage(object sender, SqlInfoMessageEventArgs args) {
            // Store the output from the SQL query message area into a buffer, to shred it   
            PageInfoBuffer = args.Message;
        }
    }
}
